--------------------------------------------------------------------------------
EXEC SP_ADDMESSAGE 50002,16,'ERRO NA PROCEDURE P_DESC_PREMIO'

SELECT * FROM MASTER.DBO.SYSMESSAGES ORDER BY 1 DESC
--------------------------------------------------------------------------------
CREATE PROCEDURE P_DESC_PREMIO
		@CODFUNC INT
AS
DECLARE 	@NUMPED 	INT,
		@VALPED	DECIMAL(10,2)

SELECT 	@VALPED=VAL_PED,
		@NUMPED=NUM_PED
FROM PEDIDO
WHERE COD_FUNC= @CODFUNC AND 
			NUM_PED=(
				SELECT MAX(NUM_PED) 
				FROM PEDIDO
				WHERE COD_FUNC=@CODFUNC)

IF @VALPED>100
BEGIN
	BEGIN TRANSACTION 

		INSERT PREMIO VALUES(
		@CODFUNC,GETDATE(),@VALPED*0.1,0)

		IF @@ERROR <>0
		BEGIN
			RAISERROR(50002,16,1)
			ROLLBACK TRANSACTION
			RETURN
		END 		

		UPDATE PEDIDO
		SET VAL_PED=VAL_PED*0.9
		WHERE NUM_PED=@NUMPED

		IF @@ERROR <>0
		BEGIN
			RAISERROR(50002,16,1)
			ROLLBACK TRANSACTION
			RETURN
		END 	

	COMMIT TRANSACTION
END
--------------------------------------------------------------------------------
SELECT * FROM PREMIO 

SELECT * FROM PEDIDO WHERE COD_FUNC = 2 ORDER BY 1 DESC

EXEC P_DESC_PREMIO 2
--------------------------------------------------------------------------------